package dbutils;

import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 一般操作
 * Created by kevin on 2020/4/2.
 */
public class Demo1{
    @Test
    public void addStu(Stu stu){
        Connection connection = null;
        PreparedStatement pstmt = null;
        try {
            connection = JdbcUtils.getConnection();
            String sql = "insert into stu values(?,?,?,?,?)";
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1,stu.getNumber());
            pstmt.setString(2,stu.getName());
            pstmt.setInt(3,stu.getAge());
            pstmt.setString(4,stu.getGender());
            pstmt.setString(5,stu.getPassword());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
        }
    }

    @Test
    public void updateStu(Stu stu){
        Connection connection = null;
        PreparedStatement pstmt = null;
        try {
            connection = JdbcUtils.getConnection();
            String sql = "update stu set name=?,age=?,gender=?,password=? where number=?";
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1,stu.getName());
            pstmt.setInt(2,stu.getAge());
            pstmt.setString(3,stu.getGender());
            pstmt.setString(4,stu.getPassword());
            pstmt.setString(5,stu.getNumber());
            pstmt.executeUpdate();
        } catch (SQLException e) {
           throw new RuntimeException(e);
        } finally {
            //关闭...略
        }
    }

    @Test
    public void deleteStu(String stuNumber){
        Connection connection = null;
        PreparedStatement pstmt = null;
        try {
            connection = JdbcUtils.getConnection();
            String sql = "delete from stu where number=?";
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1,stuNumber);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            //关闭..略
        }
    }

    @Test
    public Stu loadStu(String stuNumber){
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            connection = JdbcUtils.getConnection();
            String sql = "select * from stu where number=?";
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1,stuNumber);
            rs = pstmt.executeQuery();
            if(!rs.next()){
                return null;
            }
            Stu stu = new Stu();
            stu.setNumber(rs.getString("number"));
            //属性赋值...略
            return stu;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            //关闭..略
        }
    }
}
